﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_Model;
using HCQ2_Model.DocModel;
using HCQ2_Model.DocModel.APIModel;
using HCQ2_Model.DocModel.APIResultModel;
using HCQ2_Model.VLine.InputDto;
using HCQ2_Model.VLine.Params;
using HCQ2_Model.VR.Model;
using HCQ2_Model.VR.Params;
using HCQ2_Model.VRDD.OutputDto._0730;
using HCQ2_Model.VRDD.OutputDto._20220704;
using HCQ2_Model.VRQDMeta.InputDto._20220418;
using HCQ2_Model.VRQDMeta.OutputDto._20220418;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_DocumentFolderDAL
    {
        /// <summary>
        ///  参数
        /// </summary>
        private Dictionary<string, object> _param = new Dictionary<string, object>();
        /// <summary>
        ///  根据用户id 权限per_id获取对于的文档菜单集合
        /// </summary>
        /// <param name="user_id">用户id</param>
        /// <param name="per_id">权限id</param>
        /// <returns></returns>
        public List<T_DocumentFolder> GetDocTreeData(int user_id, int proType, List<int> per_id, bool isPrivate=false, string folder_path = "")
        {
            #region 权限获取
            if (user_id <= 0)
                return null;
            if (per_id == null || per_id.Count == 0)
            {
                per_id = new List<int>();
                per_id.Add(0);
            }
            //是否有需要排除的path
            string notPath = (!string.IsNullOrEmpty(folder_path)) ? $" AND folder_path not like '{folder_path}%'" : "";
            sb.Append($@"SELECT *  FROM (SELECT folder.* FROM 
            (SELECT folder_id FROM T_DocFolderPermissRelation WHERE per_id in({ string.Join(",", per_id).Trim('\'') })) per INNER JOIN
            (SELECT * FROM T_DocumentFolder WHERE if_remove = 0 {notPath} AND pro_type ='{proType}'
            AND if_sys = 1) folder ON per.folder_id = folder.folder_id
            UNION
            (SELECT * FROM T_DocumentFolder WHERE if_remove=0 {notPath} AND pro_type='{proType}' AND if_sys=1 AND (create_id={user_id} OR (if_limit=0 ");
            //是否私有
            if (!isPrivate)
                sb.Append(" AND if_private=0 ");//公有
            sb.Append("))))A ORDER BY case when A.folder_order is null then 1 else 0 end,A.folder_order DESC, A.folder_id DESC;");
            #endregion
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentFolder>(dt);
        }
        /// <summary>
        ///  根据用户id 权限per_id获取对于的文档菜单集合
        /// </summary>
        /// <param name="user_id">用户id</param>
        /// <param name="per_id">权限id</param>
        /// <returns></returns>
        public List<T_DocumentFolder> GetDocTreeData(int user_id,string pageType, List<int> per_id, bool isPrivate, bool isApprove = false, string folder_path = "", int docType = 0)
        {
            sb?.Clear();
            if (isApprove)
                sb.Append($"SELECT *,re_folder=(CASE WHEN is_recommend>0 THEN 1 ELSE 0 END) FROM dbo.T_DocumentFolder WHERE if_sys=1 AND if_remove = 0 AND ISNULL(page_type,'')='{pageType}' AND if_private=0");
            else
            {
                #region other
                if (user_id <= 0)
                    return null;
                if (per_id == null || per_id.Count == 0)
                {
                    per_id = new List<int>();
                    per_id.Add(0);
                }
                //是否有需要排除的path
                string notPath = (!string.IsNullOrEmpty(folder_path)) ? $" AND folder_path not like '{folder_path}%'" : "";
                sb.Append($@"SELECT *  FROM (SELECT folder.* FROM 
                        (SELECT folder_id FROM T_DocFolderPermissRelation WHERE per_id in({string.Join(",", per_id).Trim('\'')})) per INNER JOIN
                        (SELECT *,re_folder=(CASE WHEN is_recommend>0 THEN 1 ELSE 0 END) FROM T_DocumentFolder WHERE if_remove=0 {notPath} AND ISNULL(page_type,'')='{pageType}') folder ON per.folder_id=folder.folder_id
                        UNION 
                        (SELECT *,re_folder=(CASE WHEN is_recommend>0 THEN 1 ELSE 0 END) FROM T_DocumentFolder WHERE if_remove=0  AND ISNULL(page_type,'')='{pageType}' {notPath}  ");
                if(docType==3)
                    sb.Append(" AND if_sys=1 AND if_private=0 ");
                sb.Append($" AND (create_id={user_id} OR (if_limit=0 ");
                //是否私有
                if (!isPrivate)
                    sb.Append(" AND if_private=0 ");
                sb.Append($@"))))A ORDER BY case when A.folder_order is null then 1 else 0 end,A.folder_order  DESC,A.folder_id DESC;");
                #endregion
            }
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentFolder>(dt);
        }
        /// <summary>
        ///  根据商品id获取目录信息
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public T_DocumentFolder GetFolderByFileId(int file_id)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT folder.* FROM 
            (SELECT * FROM T_DocumentFolder WHERE if_remove=0)folder INNER JOIN
            (SELECT file_id,folder_id,folder_path FROM T_DocumentFolderRelation WHERE file_id={0})rel ON rel.folder_id = folder.folder_id WHERE rel.folder_path=folder.folder_path;", file_id);
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentFolder>(dt)?.FirstOrDefault();
        }
        //*********************************************接口获取商城树*********************************************
        /// <summary>
        ///  获取元宇宙素材目录导航
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<QDTreeOutput> GetQDMaterialTreeData(QDMaterialTreeInput param)
        {
            sb?.Clear();
            sb.Append($@"SELECT f.stayNum,f.folder_id AS folderID,f.folder_name AS folderName,f.folder_path AS folderPath, 
            f.folder_url AS folderImage,f.group_name AS groupName,f.bs_type AS bsType,
            f.folder_note AS folderNote,f.folder_pid AS folderPid,f.folder_order AS folderOrder,  
            folderPname=(SELECT folder_name FROM dbo.T_DocumentFolder WHERE folder_id=f.folder_pid),
            (LEN(f.folder_path)/5) AS folderLayer,CONVERT(varchar(100),f.create_time,120) AS createDate,
            CONVERT(varchar(100),f.update_date,120) AS updateDate 
            FROM dbo.T_DocumentFolder f WHERE f.page_type='{param.pageType}' AND f.if_remove=0 ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($" AND folder_path LIKE '{param.folderPath}%' ");
            if (!string.IsNullOrEmpty(param.groupName))
                sb.Append($"  AND group_name='{param.groupName}' ");
            if (param.isPush > -1)
                sb.Append($" AND is_recommend={param.isPush} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            if (!string.IsNullOrEmpty(param.roleCode))
                sb.Append($@" AND EXISTS(SELECT b.folder_id FROM dbo.T_DocumentFolder a INNER JOIN
                dbo.T_DocumentFolder b ON b.folder_path LIKE a.folder_path+'%' WHERE 
                a.if_remove=0 AND b.if_remove=0 AND a.if_sys=1 AND b.if_sys=1 AND EXISTS(
                SELECT folder_id FROM dbo.T_DocFolderPermissRelation p WHERE EXISTS(
                SELECT per_id FROM dbo.T_RolePermissRelation r WHERE EXISTS(
                SELECT role_id FROM dbo.T_Role WHERE role_code='{param.roleCode}' AND role_id=r.role_id)
                AND per_id=p.per_id) AND folder_id=a.folder_id) AND b.folder_id=f.folder_id) ");
            sb.Append(" ORDER BY f.folder_order DESC;");
            return SqlHelper.ExecuteList<QDTreeOutput>(sb.ToString());
        }
        /// <summary>
        ///  获取元宇宙城市目录导航
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<QDTreeOutput> GetQDCityTreeData(QDMaterialTreeInput param)
        {
            sb?.Clear();
            sb.Append($@"SELECT f.stayNum,f.folder_id AS folderID,f.folder_name AS folderName,f.folder_path AS folderPath, 
            f.group_name AS groupName,f.bs_type AS bsType,
            f.folder_note AS folderNote,f.folder_pid AS folderPid,f.folder_order AS folderOrder,  
            folderPname=(SELECT folder_name FROM dbo.T_DocumentFolder WHERE folder_id=f.folder_pid),
            (LEN(f.folder_path)/5) AS folderLayer,CONVERT(varchar(100),f.create_time,120) AS createDate,
            CONVERT(varchar(100),f.update_date,120) AS updateDate,f.gps_x AS gpsX,f.gps_y AS gpsY,f.floor_level AS floorLevel 
            FROM dbo.T_DocumentFolder f WHERE f.page_type='{param.pageType}' AND f.if_remove=0 ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($" AND folder_path LIKE '{param.folderPath}%' ");
            if (!string.IsNullOrEmpty(param.groupName))
                sb.Append($"  AND group_name='{param.groupName}' ");
            if (param.isPush > -1)
                sb.Append($" AND is_recommend={param.isPush} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            if (!string.IsNullOrEmpty(param.roleCode))
                sb.Append($@" AND EXISTS(SELECT b.folder_id FROM dbo.T_DocumentFolder a INNER JOIN
                dbo.T_DocumentFolder b ON b.folder_path LIKE a.folder_path+'%' WHERE 
                a.if_remove=0 AND b.if_remove=0 AND a.if_sys=1 AND b.if_sys=1 AND EXISTS(
                SELECT folder_id FROM dbo.T_DocFolderPermissRelation p WHERE EXISTS(
                SELECT per_id FROM dbo.T_RolePermissRelation r WHERE EXISTS(
                SELECT role_id FROM dbo.T_Role WHERE role_code='{param.roleCode}' AND role_id=r.role_id)
                AND per_id=p.per_id) AND folder_id=a.folder_id) AND b.folder_id=f.folder_id) ");
            sb.Append(" ORDER BY f.folder_order DESC;");
            return SqlHelper.ExecuteList<QDTreeOutput>(sb.ToString());
        }
        /// <summary>
        ///  获取目录路径列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<DocLevelTree> GetFolderPathList(DocLevelTreeParam model)
        {
            sb?.Clear();
            sb.Append($@"with temp (folderID,folderPath,folderName,folderNote,folderImage,groupName,folder_pid,folderLevel)
            as(SELECT folder_id AS folderID,folder_path AS folderPath,folder_name AS folderName,
            folder_note AS folderNote,folder_url AS folderImage,group_name AS groupName,
            folder_pid,levels AS folderLevel FROM dbo.T_DocumentFolder
            where folder_id={model.folderID}
            union all
            select a.folder_id AS folderID,a.folder_path AS folderPath,a.folder_name AS folderName,
            a.folder_note AS folderNote,a.folder_url AS folderImage,a.group_name AS groupName,
            a.folder_pid,a.levels AS folderLevel FROM dbo.T_DocumentFolder a
            inner join temp on a.folder_id = temp.folder_pid)
            select * from temp ORDER BY temp.folderLevel,temp.folderPath");
            return SqlHelper.ExecuteList<DocLevelTree>(sb.ToString());
        }
        /// <summary>
        ///  获取商城树
        /// </summary>
        /// <param name="pageType">商城类别：素材、作品，组件</param>
        /// <returns></returns>
        public List<T_DocumentGoodFolder> GetDocTreeAPIData(EmpowerTree param,int userId, List<int> per_id)
        {
            if (userId <= 0)
                userId = 0;
            if (per_id==null || per_id.Count<=0)
            {
                per_id = new List<int>();
                per_id.Add(0);
            }
            StringBuilder sb = new StringBuilder();
            StringBuilder sbParam = new StringBuilder();
            StringBuilder treeParam = new StringBuilder();
            StringBuilder roleParam = new StringBuilder();//角色
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sbParam.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            //服务类别
            if (!string.IsNullOrEmpty(param.bs_type))
                sbParam.Append($" AND bs_type='{param.bs_type}' ");
            //父目录path
            if (!string.IsNullOrEmpty(param.folder_path))
                sbParam.Append($" AND  folder_path LIKE '{param.folder_path}%' AND  folder_path<>'{param.folder_path}' ");
            //权限角色类别
            if (!string.IsNullOrEmpty(param.roleCode))
                roleParam.Append($@" AND folder_id in(SELECT DISTINCT b.folder_id FROM dbo.T_DocumentFolder a INNER JOIN
                dbo.T_DocumentFolder b ON b.folder_path LIKE a.folder_path+'%' WHERE a.page_type='{param.treeType}' AND b.page_type='{param.treeType}' AND a.if_remove=0 AND b.if_remove=0 AND a.if_sys=1 AND b.if_sys=1 AND a.folder_id IN(
	                SELECT folder_id FROM dbo.T_DocFolderPermissRelation WHERE per_id IN(
                    SELECT per_id FROM dbo.T_RolePermissRelation WHERE role_id IN(
                    SELECT role_id FROM dbo.T_Role WHERE role_code='{param.roleCode}')))) ");
            //是否推荐
            if(param.isPush>-1)
                sbParam.Append($" AND is_recommend={param.isPush} ");
            //素材格式
            if (!string.IsNullOrEmpty(param.materType))
                sbParam.Append($" AND mater_type='{param.materType}' ");
            //类别：素材：VRManager， 作品：VRWorks，组件：VRModule、度度圈：DDCircle
            if (!string.IsNullOrEmpty(param.treeType))
                sbParam.AppendFormat($" AND page_type='{param.treeType}' ");
            //所属单位
            if (!string.IsNullOrEmpty(param.unitCode))
                sbParam.AppendFormat($" AND unit_code='{param.unitCode}' ");
            //产品类别
            if (param.proType > 0)
                sbParam.AppendFormat($" AND pro_type={param.proType} ");
            //分组名称
            if (!string.IsNullOrEmpty(param.groupName))
                sbParam.AppendFormat($" AND group_name='{param.groupName}' ");

            #region 头记、校史馆
            if(param.proType==3 || param.proType == 4)
            {
                treeParam.Append(" AND if_sys=1 ");
                //私有
                if (param.treeScope == 0)
                    treeParam.Append(" AND if_private=1 ");
                //公有
                else if (param.treeScope == 0)
                    treeParam.Append(" AND if_private=0 ");
            }
            #endregion

            #region VR
            else
            {
                //是否全员共享
                if (param.treeScope == 3)
                    sbParam.AppendFormat($" AND is_share=1 ");
                //目录范围
                if (param.treeScope == -1)
                    //全部
                    treeParam.Append($" AND (create_id={userId} OR if_limit=0 OR is_share=1) ");
                else if (param.treeScope == 0)
                    //私有
                    treeParam.Append($" AND ((create_id={userId}  AND if_sys=0) OR (if_limit=0 AND if_sys=1 AND if_private=1)) ");
                else if (param.treeScope == 1)
                {
                    //公有
                    sbParam.AppendFormat($" AND if_sys=1 ");//系统节点
                    treeParam.Append($" AND if_sys=1 AND if_limit=0 AND if_private=0 ");//公有，不需要权限验证
                }
            }
            #endregion
            StringBuilder _sb = new StringBuilder();
            //判断是否获取收藏列表
            #region 获取收藏相关列表
            if (param.favorite > -1)
            {
                //1.0 获取用户收藏目录   
                CollectFolderList _param = new CollectFolderList { treeType = param.treeType, userid = param.userid };
                List<T_DocumentFolder> list = GetFolderByCollect(_param);
                string temp = param.favorite == 0 ? " NOT " : "";
                //获取已收藏
                if (param.favorite == 1 && list.Count <= 0)
                    return null;
                if (list.Count > 0)
                {
                    sbParam.Append(" AND (");
                    for (int i = 0; i < list.Count; i++)
                    {
                        if (i == 0)
                            sbParam.Append($" folder_path {temp} like '{list[i].folder_path}%' ");
                        else
                            sbParam.Append($" OR folder_path {temp} like '{list[i].folder_path}%' ");
                    }
                    sbParam.Append(")");
                }
                sb.Append($@"SELECT favorite=(CASE WHEN ISNULL(collet.rownumber,0)>0 THEN 1 ELSE 0 END),folder1.* FROM (SELECT dbo.fn_dict(pro_type,'vrProType') as proType,(SELECT COUNT(DISTINCT user_id)AS num  FROM dbo.T_VLionsRecord WHERE file_id IN(
		            SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE A.folder_path+'%'))as learn_num,(SELECT COUNT(0) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE A.folder_path+'%')AS topic_num,
		        ISNULL((SELECT SUM(result_ok) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE A.folder_path+'%'),0)AS result_ok,
		        ISNULL((SELECT SUM(result_error) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE A.folder_path+'%'),0)AS result_error,founder_phone=(SELECT user_phone FROM T_User AS tu WHERE A.folder_id=tu.[user_id]),sense=dbo.GetDistance({param.gps_x},{param.gps_y},A.gps_x,A.gps_y), *  FROM ((SELECT * FROM T_DocumentFolder WHERE if_remove=0 AND (if_sys=1 OR is_share=1) {roleParam.ToString()} {sbParam.ToString()}))A )folder1 LEFT JOIN (select folder_id,folder_date,pid=(select count(0) from T_VRUserFolder as u where T_VRUserFolder.folder_pid=u.folder_id and T_VRUserFolder.user_guid=u.user_guid),ROW_NUMBER() OVER(ORDER BY folder_date DESC) as rownumber from T_VRUserFolder where user_guid='{param.userid}')collet on  folder1.folder_id=collet.folder_id  ORDER BY case when folder1.folder_order is null THEN 1 else 0 end,folder1.folder_order  DESC,folder1.folder_id DESC;");
            }
            #endregion
            #region 正常列表
            else
            {
                string roleStr = " INNER ";
                if(!string.IsNullOrEmpty(param.roleCode))
                    roleStr = " RIGHT ";
                if (param.treeScope != 0)
                {
                    _sb.Append($@"SELECT folder.* FROM 
                        (SELECT folder_id FROM T_DocFolderPermissRelation WHERE per_id in({string.Join(",", per_id).Trim('\'')})) per {roleStr} JOIN
                        (SELECT (SELECT COUNT(DISTINCT user_id)AS num  FROM dbo.T_VLionsRecord WHERE file_id IN(
		            SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%'))as learn_num,(SELECT COUNT(0) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%')AS topic_num,
		        ISNULL((SELECT SUM(result_ok) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),0)AS result_ok,
		        ISNULL((SELECT SUM(result_error) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),0)AS result_error,founder_phone=(SELECT user_phone FROM T_User AS tu WHERE f.folder_id=tu.[user_id]),sense=dbo.GetDistance({param.gps_x},{param.gps_y},f.gps_x,f.gps_y), * FROM T_DocumentFolder f WHERE if_remove=0 {roleParam.ToString()} {sbParam.ToString()}) folder ON per.folder_id=folder.folder_id");
                }
                sb.Append($@"SELECT favorite=(CASE WHEN ISNULL(collet.rownumber,0)>0 THEN 1 ELSE 0 END),folder1.* FROM (SELECT DISTINCT dbo.fn_dict(pro_type,'vrProType') as proType,*  FROM ({_sb.ToString()} ");
                //获取不需要权限验证的数据、当角色代码为空时才获取不需要权限验证的数据
                //当传递folder_path时、忽略角色代码
                if (string.IsNullOrEmpty(param.roleCode) || !string.IsNullOrEmpty(param.folder_path))
                {
                    //根据权限获取
                    if(!string.IsNullOrEmpty(_sb.ToString()))
                        sb.Append(" UNION ");
                    sb.Append($@" SELECT (SELECT COUNT(DISTINCT user_id)AS num  FROM dbo.T_VLionsRecord WHERE file_id IN(
		            SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%'))as learn_num,(SELECT COUNT(0) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%')AS topic_num,                    
		            ISNULL((SELECT SUM(result_ok) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),0)AS result_ok,
		            ISNULL((SELECT SUM(result_error) FROM dbo.T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),0)AS result_error,founder_phone=(SELECT user_phone FROM T_User AS tu WHERE f.folder_id=tu.[user_id]),sense=dbo.GetDistance({param.gps_x},{param.gps_y},f.gps_x,f.gps_y),* FROM T_DocumentFolder f WHERE if_remove=0  {sbParam.ToString() + treeParam.ToString()}");
                    //是否推荐
                    if (param.isPush > -1)
                        sbParam.Append($" AND is_recommend={param.isPush} ");
                }
                sb.Append($@")A
               )folder1 LEFT JOIN (select folder_id,folder_date,pid=(select count(0) from T_VRUserFolder as u where T_VRUserFolder.folder_pid=u.folder_id and 
                T_VRUserFolder.user_guid=u.user_guid),ROW_NUMBER() OVER(ORDER BY folder_date DESC) as rownumber from T_VRUserFolder where user_guid='{param.userid}')collet ON folder1.folder_id=collet.folder_id
                ORDER BY case when folder1.folder_order is null then 1 else 0 end,folder1.folder_order  DESC,folder1.folder_id DESC;");
            }
            #endregion
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentGoodFolder>(dt);
        }
        /// <summary>
        ///   获取VR端 头记相关目录
        /// </summary>
        /// <param name="pageType">商城类别：素材、作品，组件</param>
        /// <returns></returns>
        public List<T_DocumentGoodFolder> GetDocTreeAPIData(EmpowerTree param)
        {
            #region 不包含统计注释
            //sb?.Clear();
            //sb.Append($@"SELECT dbo.fn_dict(pro_type,'vrProType') as proType,* FROM dbo.T_DocumentFolder WHERE  if_sys=1 AND if_remove=0 ");
            ////关键字
            //if (!string.IsNullOrEmpty(param.keyword))
            //    sb.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            ////父目录path
            //if (!string.IsNullOrEmpty(param.folder_path))
            //    sb.Append($" AND  folder_path LIKE '{param.folder_path}%' AND  folder_path<>'{param.folder_path}' ");
            ////是否推荐
            //if (param.isPush != null)
            //    sb.Append($" AND is_recommend={param.isPush} ");
            ////产品类别
            //if (param.proType > 0)
            //    sb.Append($" AND pro_type={param.proType} ");
            ////树类别：素材：VRManager， 作品：VRWorks，组件：VRModule、度度圈：DDCircle
            //if (!string.IsNullOrEmpty(param.treeType))
            //    sb.AppendFormat($" AND page_type='{param.treeType}' ");
            ////素材格式
            //if (!string.IsNullOrEmpty(param.materType))
            //    sb.Append($" AND mater_type='{param.materType}' ");
            //sb.Append($" ORDER BY CASE WHEN folder_order is null then 1 else 0 end,folder_order DESC,folder_id DESC;");
            //DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            //return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentGoodFolder>(dt);
            #endregion
            sb?.Clear();
            sb.Append($@"SELECT folder.*,ISNULL(learn_num,0)AS learn_num,ISNULL(topic_num,0)AS topic_num,ISNULL(result_ok,0)AS result_ok,ISNULL(result_error,0)AS result_error FROM (SELECT dbo.fn_dict(pro_type,'vrProType') as proType,* FROM dbo.T_DocumentFolder WHERE  if_sys=1 AND if_remove=0");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            //父目录path
            if (!string.IsNullOrEmpty(param.folder_path))
                sb.Append($" AND  folder_path LIKE '{param.folder_path}%' AND  folder_path<>'{param.folder_path}' ");
            //是否推荐
            if (param.isPush >-1)
                sb.Append($" AND is_recommend={param.isPush} ");
            //产品类别
            if (param.proType > 0)
                sb.Append($" AND pro_type={param.proType} ");
            //树类别：素材：VRManager， 作品：VRWorks，组件：VRModule、度度圈：DDCircle
            if (!string.IsNullOrEmpty(param.treeType))
                sb.AppendFormat($" AND page_type='{param.treeType}' ");
            //素材格式
            if (!string.IsNullOrEmpty(param.materType))
                sb.Append($" AND mater_type='{param.materType}' ");
            sb.Append($@" OR (is_share=1))folder LEFT JOIN (SELECT folder_path,SUM(learn_num)AS learn_num,SUM(topic_num)AS topic_num,SUM(result_ok)AS result_ok,SUM(result_error)AS result_error
            FROM T_VRExperienceWorkMain GROUP BY folder_path)main ON folder.folder_path = main.folder_path ORDER BY CASE WHEN folder_order is null then 1 else 0 end,folder_order DESC, folder_id DESC; ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentGoodFolder>(dt);
        }

        //********************************************************************************************************
        /// <summary>
        ///  获取导航树统计
        /// </summary>
        /// <param name="pageType">商城类别：素材、作品</param>
        /// <returns></returns>
        public List<TreeDataStatistics> GetTreeStatisticsAPI(EmpowerTree param, int userId, List<int> per_id)
        {
            if (userId <= 0)
                userId = 0;
            if (userId <= 0 || per_id == null || per_id.Count <= 0)
            {
                per_id = new List<int>();
                per_id.Add(0);
            }
            StringBuilder sb = new StringBuilder();
            StringBuilder sbParam = new StringBuilder();
            StringBuilder treeParam = new StringBuilder();
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sbParam.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            //父目录path
            if (!string.IsNullOrEmpty(param.folder_path))
                sbParam.Append($" AND  folder_path LIKE '{param.folder_path}%' AND  folder_path<>'{param.folder_path}' ");
            //是否推荐
            if (param.isPush >-1)
                sbParam.Append($" AND is_recommend={param.isPush} ");
            //素材格式
            if (!string.IsNullOrEmpty(param.materType))
                sbParam.Append($" AND mater_type='{param.materType}' ");
            //类别：素材：VRManager， 作品：VRWorks，组件：VRModule、度度圈：DDCircle
            if (!string.IsNullOrEmpty(param.treeType))
                sbParam.AppendFormat($" AND page_type='{param.treeType}' ");
            //所属单位
            if (!string.IsNullOrEmpty(param.unitCode))
                sbParam.AppendFormat($" AND unit_code='{param.unitCode}' ");
            //产品类别
            if (param.proType > 0)
                sbParam.AppendFormat($" AND pro_type={param.proType} ");
            //目录范围
            if (param.treeScope == -1)
                //全部
                treeParam.Append($" AND (create_id={userId} OR if_limit=0) ");
            else if (param.treeScope == 0)
                //私有
                treeParam.Append($" AND (create_id={userId} OR (if_limit=0 AND if_private=1)) ");
            else if (param.treeScope == 1)
            {
                //公有
                sbParam.AppendFormat($" AND if_sys=1 ");//系统节点
                treeParam.Append($" AND if_limit=0 AND if_private=0 ");//公有，不需要权限验证
            }
            StringBuilder _sb = new StringBuilder();
            if (param.treeScope != 0)
                _sb.Append($@"SELECT folder.* FROM 
                        (SELECT folder_id FROM T_DocFolderPermissRelation WHERE per_id in({string.Join(",", per_id).Trim('\'')})) per INNER JOIN
                        (SELECT folder_id,folder_pid,folder_order,folder_path FROM T_DocumentFolder WHERE if_remove=0 {sbParam.ToString()}) folder ON per.folder_id=folder.folder_id
                        UNION ");
            sb.Append($@"SELECT folder1.*,ISNULL(learn_num,0)AS learn_num,ISNULL(topic_num,0)AS topic_num,ISNULL(result_ok,0)AS result_ok,ISNULL(result_error,0)AS result_error FROM (SELECT *  FROM ({_sb.ToString()}
                        (SELECT folder_id,folder_pid,folder_order,folder_path FROM T_DocumentFolder WHERE if_remove=0  {sbParam.ToString() + treeParam.ToString()} ))A )folder1 LEFT JOIN (SELECT folder_path,SUM(learn_num)AS learn_num,SUM(topic_num)AS topic_num,SUM(result_ok)AS result_ok,SUM(result_error)AS result_error FROM T_VRExperienceWorkMain GROUP BY folder_path)main ON folder1.folder_path=main.folder_path  ORDER BY case when folder1.folder_order is null then 1 else 0 end,folder1.folder_order  DESC,folder1.folder_id DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<TreeDataStatistics>(dt);
        }
        /// <summary>
        ///  获取VR端 头记相关目录统计数据
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<TreeDataStatistics> GetTreeStatisticsAPI(EmpowerTree param)
        {
            sb?.Clear();
            sb.Append($@"SELECT folder.folder_id,folder.folder_pid,accuracy=0,ISNULL(learn_num,0)AS learn_num,ISNULL(topic_num,0)AS topic_num,ISNULL(result_ok,0)AS result_ok,ISNULL(result_error,0)AS result_error FROM (SELECT folder_id,folder_pid,folder_path,folder_order FROM dbo.T_DocumentFolder WHERE  if_sys=1 AND if_remove=0 ");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            //父目录path
            if (!string.IsNullOrEmpty(param.folder_path))
                sb.Append($" AND  folder_path LIKE '{param.folder_path}%' AND  folder_path<>'{param.folder_path}' ");
            //是否推荐
            if (param.isPush >-1)
                sb.Append($" AND is_recommend={param.isPush} ");
            //产品类别
            if (param.proType > 0)
                sb.Append($" AND pro_type={param.proType} ");
            //树类别：素材：VRManager， 作品：VRWorks，组件：VRModule、度度圈：DDCircle
            if (!string.IsNullOrEmpty(param.treeType))
                sb.AppendFormat($" AND page_type='{param.treeType}' ");
            //素材格式
            if (!string.IsNullOrEmpty(param.materType))
                sb.Append($" AND mater_type='{param.materType}' ");
            sb.Append($@")folder LEFT JOIN (SELECT folder_path,SUM(learn_num)AS learn_num,SUM(topic_num)AS topic_num,SUM(result_ok)AS result_ok,SUM(result_error)AS result_error FROM T_VRExperienceWorkMain GROUP BY folder_path)main ON folder.folder_path = main.folder_path ORDER BY CASE WHEN folder_order is null then 1 else 0 end,folder_order DESC,folder_id DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<TreeDataStatistics>(dt);
        }

        //********************************************************************************************************
        /// <summary>
        ///  获取目录以及子目录下课件数量，判断是否允许删除目录
        /// </summary>
        /// <param name="folderPath"></param>
        /// <returns></returns>
        public int GetFolderFileNum(string folderPath)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) AS num FROM 
            (SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{folderPath}%')rel INNER JOIN
            (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0)info ON info.file_id = rel.file_id;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取目录详情
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<FolderDetials> GetFoldersDetail(FolderDetialModel model,string folder_path)
        {
            sb.Append($@"SELECT folder_id as id,deliveryDistance,sortType,city,folder_name,bs_url,bs_note,statr_partner,enLang,have_child,folder_pid as pId,folder_path,unit_code,
            unit_name=(SELECT code_name FROM dbo.T_ItemCodeMenum WHERE code_value=f.unit_code AND item_id IN(
	        SELECT item_id FROM dbo.T_ItemCode WHERE item_code='SysUserUnit')),CAST(if_private AS INT)AS if_private,
            pro_type,dbo.GetDictText('vrProType',pro_type)AS proType,deal_note=(select top 1 deal_note from T_VRFounderDeal d where f.folder_id=d.folder_id)
            ,group_name,is_recommend,mater_type,CAST(is_share AS INT)AS is_share,folder_order,star_num,focus_num,folder_note,folder_url,
            founder_id,founder_name,founder_login,deal_note=(SELECT deal_note FROM dbo.T_VRFounderDeal WHERE create_id={model.create_id} AND folder_id=f.folder_id),
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolderStar WHERE folder_id=f.folder_id AND user_guid='{model.userid}')
            ,gps_x,gps_y,bs_type,addr,addr AS bs_address,sense=(dbo.GetDistance({model.gps_x},{model.gps_y},gps_x,gps_y)),
            founderid=(SELECT user_guid FROM dbo.T_User WHERE user_id=f.founder_id),      
            dealId=(SELECT TOP 1 id FROM dbo.T_VRFounderDeal WHERE folder_id=f.folder_id),
            founder_phone=(SELECT user_phone FROM dbo.T_User WHERE user_id=f.founder_id),CONVERT(varchar(100),create_time,120) AS create_date,
            create_name,CONVERT(varchar(100),update_date,120) AS update_date,update_name,floor_level,partner_status=-1
            ,is_collect=(SELECT CASE WHEN COUNT(0)>0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END FROM T_VRUserFolder WHERE folder_id=f.folder_id AND 
            user_guid='{model.userid}'),store_type=(SELECT stuff((select ',' + store_type from dbo.T_VRDDStoreTypeUser WHERE store_id = f.folder_id AND 
            dd_id={model.create_id} for xml path('')), 1, 1, '') FROM dbo.T_VRDDStoreTypeUser a WHERE store_id=f.folder_id AND dd_id={model.create_id}
            GROUP BY a.store_id) FROM dbo.T_DocumentFolder f WHERE folder_path LIKE '{folder_path}%';");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<FolderDetials>(dt);
        }
        /// <summary>
        ///  获取用户收藏目录列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<FolderResult> GetUserFolderCollectList(CollectFolderList model)
        {        
            //1.0 获取用户收藏目录   
            List<T_DocumentFolder> list = GetFolderByCollect(model);
            sb?.Clear();
            sb.Append($"select f.folder_name,f.folder_note,f.folder_url,f.folder_path,f.folder_id,f.folder_pid,cast(isnull(rownumber,0) as int)as rownumber,cast(isnull(pid,100) as int)as pid from (select * from T_DocumentFolder where page_type='{model.treeType}'  ");
            if (list.Count > 0)
            {
                sb.Append(" and (");
                for (int i = 0; i < list.Count; i++)
                {
                    if (i == 0)
                        sb.Append($" folder_path like '{list[i].folder_path}%' ");
                    else
                        sb.Append($" or folder_path like '{list[i].folder_path}%' ");
                }
                sb.Append($"))f left join (select folder_id,folder_date,pid=(select count(0) from T_VRUserFolder as u where T_VRUserFolder.folder_pid=u.folder_id and T_VRUserFolder.user_guid=u.user_guid),ROW_NUMBER() OVER(ORDER BY folder_date DESC) as rownumber from T_VRUserFolder where user_guid='{model.userid}')collet on f.folder_id=collet.folder_id order by collet.folder_date desc;");
            }
            else
                return null;
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return  HCQ2_Common.Data.DataTableHelper.DataTableToIList<FolderResult>(dt);
        }
        /// <summary>
        ///  获取用户收藏目录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<T_DocumentFolder> GetFolderByCollect(CollectFolderList model)
        {
            sb?.Clear();
            sb.Append($@"select folder.* from 
            (select * from T_DocumentFolder where page_type='{model.treeType}')folder inner join
            (select folder_id,folder_date from T_VRUserFolder where user_guid='{model.userid}')collect on folder.folder_id=collect.folder_id
            order by collect.folder_date desc;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentFolder>(dt);
        }
        /// <summary>
        ///  获取用户权限内可看目录
        /// </summary>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<T_DocumentFolder> GetUserFolderPermiss(int user_id)
        {
            //目录权限
            string per_type="";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count>0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            sb?.Clear();
            sb.Append($@"SELECT fodler1.* FROM 
            (SELECT * FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
            (SELECT * FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
            UNION
            SELECT * FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_DocumentFolder>(dt);
        }
        /// <summary>
        ///  获取用户关注目录排行
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<UserFolderRank> GetUserStarFolderRank(FavoriteRankParam param, int user_id)
        {
            string per_type = "";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count > 0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            StringBuilder temp = new StringBuilder();
            //分组名称
            if (!string.IsNullOrEmpty(param.groupName))
                temp.Append($" AND group_name='{param.groupName}' ");
            //开始时间
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' AND create_time<='{1}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time<= '{0}' ", DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} star.*,folder.folder_name FROM 
            (SELECT COUNT(0) AS starNum,folder_id FROM T_VRUserFolder WHERE folder_id IN(
	            SELECT fodler1.folder_id FROM 
	            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1 {temp.ToString()})fodler1 INNER JOIN
	            (SELECT folder_id FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
	            UNION
	            SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0 {temp.ToString()}
            ) GROUP BY folder_id)star INNER JOIN
            (SELECT * FROM dbo.T_DocumentFolder)folder ON folder.folder_id = star.folder_id ORDER BY star.starNum DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserFolderRank>(dt);
        }
        /// <summary>
        ///  获取用户关注作品排行
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<UserWorkStar> GetUserStarWorkRank(FavoriteRankParam param, int user_id)
        {
            string per_type = "";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count > 0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            StringBuilder temp = new StringBuilder();
            //开始时间
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' AND create_time<='{1}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time<= '{0}' ", DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} star.*,info.file_name,info.file_code FROM 
            (SELECT fodler1.folder_id FROM 
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
            (SELECT folder_id FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
            UNION
            SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0)folder
            INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation)rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_code FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5 {temp.ToString()})info ON info.file_id = rel.file_id INNER JOIN
            (SELECT COUNT(0) AS starNum,file_id FROM T_DocumentUserStar GROUP BY file_id)star ON star.file_id = info.file_id
             ORDER BY star.starNum DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserWorkStar>(dt);
        }
        /// <summary>
        ///  获取作品下载排行
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<UserWorkStar> GetWorkDownloadRank(FavoriteRankParam param, int user_id)
        {
            string per_type = "";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count>0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            StringBuilder temp = new StringBuilder();
            //产品类别
            if (!string.IsNullOrEmpty(param.proType))
                temp.Append($" AND pro_type IN(${param.proType.Trim(',').Trim('，').Replace('，', ',')}) ");
            //开始时间
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' AND create_time<='{1}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time<= '{0}' ", DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} info.* FROM 
            (SELECT fodler1.folder_id FROM 
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
            (SELECT folder_id FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
            UNION
            SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0)folder
            INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation)rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_code,down_number AS starNum  FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5 {temp.ToString()})info ON info.file_id = rel.file_id 
             ORDER BY info.starNum DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserWorkStar>(dt);
        }
        /// <summary>
        ///  获取作品学习排行
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<UserWorkStar> GetWorkLearnRank(FavoriteRankParam param, int user_id)
        {
            string per_type = "";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count>0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            StringBuilder temp = new StringBuilder();
            //开始时间
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' AND create_time<='{1}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time<= '{0}' ", DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} info.*,record.starNum FROM 
            (SELECT fodler1.folder_id FROM 
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
            (SELECT folder_id FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
            UNION
            SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0)folder
            INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation)rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_code FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5 {temp.ToString()})info ON info.file_id = rel.file_id INNER JOIN
            (SELECT SUM(CAST(ISNULL(em_time,0) AS INT))AS starNum,file_id FROM dbo.T_VRExperienceRecord WHERE re_type=1 ");
            //是否查询个人数据
            if (param.isPerson > 0  && user_id>0)
                sb.Append($" AND user_id={user_id} ");
            sb.Append(" GROUP BY file_id)record ON record.file_id = info.file_id ORDER BY record.starNum DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserWorkStar>(dt);
        }
        /// <summary>
        ///  获取用户学习排行
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<UserLearnRank> GetUserLearnRank(FavoriteRankParam param, int user_id)
        {
            string per_type = "";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count>0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            StringBuilder temp = new StringBuilder();
            //开始时间
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' AND create_time<='{1}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time<= '{0}' ", DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} record.*,users.user_name,users.login_name FROM 
            (SELECT fodler1.folder_id FROM 
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
            (SELECT folder_id FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
            UNION
            SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0)folder
            INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation)rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_code FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5 {temp.ToString()})info ON info.file_id = rel.file_id INNER JOIN
            (SELECT SUM(CAST(ISNULL(em_time,0) AS INT))AS learnNum,file_id,user_id FROM dbo.T_VRExperienceRecord WHERE re_type=1 GROUP BY file_id,user_id)record ON record.file_id = info.file_id INNER JOIN
            (SELECT user_id,user_name,login_name FROM dbo.T_User)users ON users.user_id = record.user_id
             ORDER BY record.learnNum DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserLearnRank>(dt);
        }
        /// <summary>
        ///  获取用户答题正确率
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<UserAccuracyRank> GetUserOkAccuracyRank(FavoriteRankParam param, int user_id)
        {
            string per_type = "";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count>0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            StringBuilder temp = new StringBuilder();
            //开始时间
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' AND create_time<='{1}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time<= '{0}' ", DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} * FROM (SELECT users.*,accuracy=(CASE WHEN SUM(main.result_error)<=0 THEN 100 ELSE CONVERT(INT,Convert(decimal(18,2),Convert(decimal(18,2),SUM(main.result_ok))/Convert(decimal(18,2),SUM(main.result_ok)+SUM(main.result_error)))*100) END) FROM 
            (SELECT fodler1.folder_id FROM 
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
            (SELECT folder_id FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
            UNION
            SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0)folder
            INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation)rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_code FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5 {temp.ToString()})info ON info.file_id = rel.file_id INNER JOIN
            (SELECT file_id,user_id,result_ok,result_error FROM T_VRExperienceRecordMain)main ON main.file_id=info.file_id  INNER JOIN
            (SELECT user_id,user_name,login_name FROM dbo.T_User)users ON users.user_id = main.user_id GROUP BY users.user_id,users.user_name,users.login_name)u ORDER BY u.accuracy DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserAccuracyRank>(dt);
        }
        /// <summary>
        ///  获取错题目排行
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<LinesErrorRank> GetLinesErrorRank(FavoriteRankParam param, int user_id)
        {
            string per_type = "";
            List<int> list = new List<int>();
            List<T_Permissions> query = new T_PermissionsDAL().GetPermissById(user_id, "docMenu")?.ToList();
            if (query != null && query.Count>0)
                query.ForEach(o => list.Add(o.per_id));
            if (list.Count > 0)
                per_type = $"  WHERE per_id IN({string.Join(",", list.ToArray())})";
            StringBuilder temp = new StringBuilder();
            //开始时间
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' AND create_time<='{1}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time>= '{0}' ", DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                temp.AppendFormat(" AND create_time<= '{0}' ", DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} * FROM 
            (SELECT COUNT(0)AS errNum,workMain.content AS title FROM(
            (SELECT fodler1.folder_id FROM 
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
            (SELECT folder_id FROM dbo.T_DocFolderPermissRelation {per_type})rel ON rel.folder_id = fodler1.folder_id
            UNION
            SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='VRWorks' AND if_sys=1 AND if_remove=0 AND if_limit=0)folder
            INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation)rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_code FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5 {temp.ToString()})info ON info.file_id = rel.file_id INNER JOIN
            (SELECT file_id,er_id FROM dbo.T_VLionsRecord WHERE 1=1 ");
            if (param.isPerson>0 && user_id > 0)
                sb.Append($" AND user_id={user_id} ");
            sb.Append($@")record ON record.file_id = info.file_id INNER JOIN
            (SELECT er_id,wm_id,sel_result FROM dbo.T_VLinesExperienceDetail WHERE status=0)detial ON detial.er_id = record.er_id INNER JOIN
            (SELECT wm_id,content FROM dbo.T_VRExperienceWorkMain)workMain ON detial.wm_id=workMain.wm_id) 
             GROUP BY workMain.content)main ORDER BY main.errNum DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<LinesErrorRank>(dt);
        }
        /// <summary>
        ///  验证pid是否属于folder_path的只目录
        /// </summary>
        /// <param name="fodler_path"></param>
        /// <param name="pid"></param>
        /// <returns></returns>
        public int CheckParentTree(string folder_path, int pid)
        {
            sb?.Clear();
            sb.Append($"SELECT COUNT(0) AS num FROM dbo.T_DocumentFolder WHERE folder_path LIKE '{folder_path}%' AND folder_id={pid};");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  判断当前目录下是否有子目录
        /// </summary>
        /// <param name="folder_path"></param>
        /// <returns></returns>
        public int CheckIsChild(string folder_path)
        {
            sb?.Clear();
            sb.Append($"SELECT COUNT(0)AS num FROM dbo.T_DocumentFolder WHERE folder_path<>'{folder_path}' AND folder_path LIKE '{folder_path}%';");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  判断当前目录是否有课件
        /// </summary>
        /// <param name="folder_id"></param>
        /// <returns></returns>
        public int CheckIsWorks(int folder_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM 
            (SELECT file_id FROM dbo.T_DocumentInfo WHERE file_status <> 4)info INNER JOIN
            (SELECT file_id, folder_id FROM dbo.T_DocumentFolderRelation WHERE folder_id = {folder_id})rel ON rel.file_id = info.file_id; ");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }

        /// <summary>
        ///  根据id获取path
        /// </summary>
        /// <param name="folderIds"></param>
        /// <returns></returns>
        public List<string> GetFolderPathByIds(List<int> folderIds)
        {
            if (folderIds == null || folderIds.Count <= 0)
                return null;
            var query = (from o in db.Set<T_DocumentFolder>()
                                         where folderIds.Contains(o.folder_id)
                                         select new List<string> { o.folder_path }).AsEnumerable();
            List<string> list = new List<string>();
            foreach (var item in query)
                list.Add(item.ToString());
            return list;
        }

        /// <summary>
        ///  获取作品数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public int GetWorkNum(WorkNumInputDto dto,int user_id,List<string> folderPath)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM (SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE 1=1 ");
            //用户
            if (user_id > 0)
                sb.Append($" AND create_id={user_id} ");
            //folder_path
            if (folderPath != null && folderPath.Count > 0)
            {
                sb.Append(" AND (");
                for(int i=0;i< folderPath.Count; i++)
                {
                    if (i == (folderPath.Count - 1))
                        sb.Append($" folder_path LIKE '{folderPath[i]}%' ");
                    else
                        sb.Append($" folder_path LIKE '{folderPath[i]}%' OR ");
                }
                sb.Append(") ");
            }
            sb.Append(")rel INNER JOIN (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 ");
            //用户
            if (user_id > 0)
                sb.Append($" AND create_id={user_id} ");
            //状态
            if(dto.fileStatus==0)
                sb.Append($" AND file_status=5 ");//公开
            else if (dto.fileStatus == 1)
                sb.Append($" AND file_status=0 ");//私有
            sb.Append(")info ON info.file_id = rel.file_id;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取目录数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public int GetFolderNum(FolderNumInputDto dto, List<int> per_id)
        {
            sb?.Clear();
            if (dto.isPerson > 0)
                //我共享的私有目录数【私有共享】
                sb.Append($@"SELECT COUNT(0)AS num FROM dbo.T_DocumentFolder WHERE if_remove=0 AND is_share=1 AND if_private=1  {GetMyFloderStr(dto)}");
            else
            {
                //我能看到的公开目录数
                sb.Append($@"SELECT COUNT(0)AS num FROM (SELECT fodler1.folder_id FROM  
	                (SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_sys=1 AND if_remove=0 AND if_limit=1 {GetMyFloderStr(dto)})fodler1 INNER JOIN
	                (SELECT folder_id FROM dbo.T_DocFolderPermissRelation WHERE per_id IN({string.Join(",", per_id).Trim('\'')}))rel ON rel.folder_id = fodler1.folder_id
	                UNION
	                SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_sys=1 AND if_remove=0 AND if_limit=0 {GetMyFloderStr(dto)} )f");
            }
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        private string GetMyFloderStr(FolderNumInputDto dto)
        {
            StringBuilder _sb = new StringBuilder();
            //是否个人数据
            if (dto.isPerson > 0 && dto.user_id > 0)
                _sb.Append($" AND create_id={dto.user_id} ");
            //分组名称
            if (!string.IsNullOrEmpty(dto.groupName))
                _sb.Append($" AND group_name='{dto.groupName}' ");
            //产品类别
            if (dto.proType > 0)
                _sb.Append($" AND pro_type={dto.proType} ");
            //目录类别
            if (!string.IsNullOrEmpty(dto.treeType))
                _sb.Append($" AND page_type='{dto.treeType}' ");
            return _sb.ToString();
        }
        /// <summary>
        ///  获取上课人数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public int GetLearnNum(LearnClassNumInputDto dto)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM dbo.N_VRUserAttend WHERE RmId IN(SELECT RmId FROM dbo.N_VRLineClassRoom WHERE 1=1 ");
            if (dto.ifType > -1)
                sb.Append($" AND IfFinish={dto.ifType} ");
            //时间
            if (!string.IsNullOrEmpty(dto.startDate) && !string.IsNullOrEmpty(dto.endDate))
                sb.AppendFormat(" AND StartTime>= '{0}' AND StartTime<='{1}' ", DateTime.ParseExact(dto.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(dto.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(dto.startDate) && string.IsNullOrEmpty(dto.endDate))
                sb.AppendFormat(" AND StartTime>= '{0}' ", DateTime.ParseExact(dto.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(dto.startDate) && !string.IsNullOrEmpty(dto.endDate))
                sb.AppendFormat(" AND StartTime<= '{0}' ", DateTime.ParseExact(dto.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb.Append(")");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取直播课堂数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public int GetLinesClassNum(LearnClassNumInputDto dto, List<int> per_id)
        {
            sb?.Clear();
            if(dto.isPerson<=0 && dto.user_id>0)
                //能看到的
                sb.Append($@"SELECT COUNT(0)AS num FROM (SELECT RmId FROM dbo.N_VRLineClassRoom WHERE FolderId IN(SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_remove=0) ");
            else
                sb.Append($@"SELECT COUNT(0)AS num FROM dbo.N_VRLineClassRoom WHERE FolderId IN(SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_remove=0) ");
            sb.Append(GetLinesClassNumStr(dto));
            if (dto.isPerson <= 0 && dto.user_id > 0)
                sb.Append($@")room INNER JOIN
                (SELECT DISTINCT RmId FROM 
                (SELECT RmId,FileId FROM dbo.N_VRLineClassRoomFile)files INNER JOIN
                (
	                SELECT doc.file_id FROM 
	                (SELECT fodler1.folder_id FROM 
	                (SELECT folder_id FROM dbo.T_DocumentFolder WHERE  if_sys=1 AND if_remove=0 AND if_limit=1)fodler1 INNER JOIN
	                (SELECT folder_id FROM dbo.T_DocFolderPermissRelation WHERE per_id IN({string.Join(",", per_id).Trim('\'')}))rel ON rel.folder_id = fodler1.folder_id
	                UNION
	                SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_sys=1 AND if_remove=0 AND if_limit=0)folder INNER JOIN
	                (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation)doc ON doc.folder_id = folder.folder_id
                )a ON files.FileId=a.file_id)c ON room.RmId=c.RmId;");  
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        private string GetLinesClassNumStr(LearnClassNumInputDto dto)
        {
            StringBuilder _sb = new StringBuilder();
            //是否个人数据
            if (dto.isPerson > 0 && dto.user_id > 0)
                sb.Append($" AND CreateId={dto.user_id} ");
            if (dto.ifType > -1)
                _sb.Append($" AND IfFinish={dto.ifType} ");
            //时间
            if (!string.IsNullOrEmpty(dto.startDate) && !string.IsNullOrEmpty(dto.endDate))
                _sb.AppendFormat(" AND StartTime>= '{0}' AND StartTime<='{1}' ", DateTime.ParseExact(dto.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(dto.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(dto.startDate) && string.IsNullOrEmpty(dto.endDate))
                _sb.AppendFormat(" AND StartTime>= '{0}' ", DateTime.ParseExact(dto.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(dto.startDate) && !string.IsNullOrEmpty(dto.endDate))
                _sb.AppendFormat(" AND StartTime<= '{0}' ", DateTime.ParseExact(dto.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            return _sb.ToString();
        }
        /// <summary>
        ///  获取我的店铺
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<MyFloderModel> GetMyFloderList(MyFloderParam param, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM(SELECT folder_id,folder_path,folder_name,folder_url,folder_note AS folderNote,
            ROW_NUMBER() OVER(ORDER BY update_date DESC)AS num 
            ,isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolder WHERE folder_id=f.folder_id AND user_guid='{param.loginUserID}')
            ,isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolderStar WHERE folder_id=f.folder_id AND user_guid='{param.loginUserID}')
            ,f.focus_num AS holdNum,f.star_num AS starNum,f.statr_partner AS partnerEnabled,
            f.founder_name AS founderName,f.founder_id AS founderID,partnerStatus=-1
            ,store_type=(SELECT stuff((select ',' + store_type from T_VRDDStoreTypeUser 
			WHERE store_id = f.folder_id AND dd_id={user_id} for xml path('')), 1, 1, '')
			FROM T_VRDDStoreTypeUser a WHERE store_id=f.folder_id AND dd_id={user_id} GROUP BY a.store_id)
            FROM dbo.T_DocumentFolder f WHERE if_remove=0 AND group_name='store' ");
            string type1 = $" founder_id={user_id}  ",
                type2 = $" folder_id IN(SELECT folder_id FROM dbo.T_VRFounderApply WHERE apply_status=1 AND user_id={user_id}) ",
                type3 = $" folder_id IN(SELECT folder_id FROM T_VRUserFolder WHERE user_guid='{param.userid}') ",
                type4 = $" folder_id IN(SELECT store_id FROM dbo.T_VRDDStoreTypeUser WHERE dd_id={user_id}) ";//服务的店铺
            switch (param.type)
            {
                case 0: sb.Append($" AND ({type1} OR {type2} OR {type4}) "); break;
                case 1: sb.Append($" AND {type1} "); break;
                case 2: sb.Append($" AND {type2} "); break;
                case 3: sb.Append($" AND {type3} "); break;
                case 4:sb.Append($" AND {type4} ");break;//服务的店铺
                default: sb.Append($" AND ({type1} OR {type2} OR {type3} OR {type4} ) "); break;
            }
            sb.Append($")folder WHERE folder.num>{(param.page - 1) * param.rows}");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<MyFloderModel>(dt);
        }
        /// <summary>
        ///  获取所有店铺
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<MyFloderModel> GetAllFloderList(AllFloderParam param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM(SELECT folder_id,folder_path,folder_name,folder_url,folder_note AS folderNote,
            ROW_NUMBER() OVER(ORDER BY update_date DESC)AS num 
            ,isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolder WHERE folder_id=f.folder_id AND user_guid='{param.userid}')
            ,isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolderStar WHERE folder_id=f.folder_id AND user_guid='{param.userid}')
            ,f.founder_id AS founderID,f.founder_name AS founderName,f.focus_num AS holdNum,
            f.statr_partner AS partnerEnabled,partnerStatus=-1,f.star_num AS starNum 
            ,store_type=(SELECT stuff((select ',' + store_type from T_VRDDStoreTypeUser 
            WHERE store_id = f.folder_id AND dd_id={user_id} for xml path('')), 1, 1, '')
            FROM T_VRDDStoreTypeUser a WHERE store_id=f.folder_id AND dd_id={user_id} GROUP BY a.store_id)
            FROM dbo.T_DocumentFolder f WHERE if_remove=0 AND group_name='store' ");
            //是否推荐
            if (param.isPush > -1)
                sb.Append($" AND is_recommend={param.isPush} ");
            //path
            if (!string.IsNullOrEmpty(param.folder_path))
                sb.Append($" AND folder_path LIKE '{param.folder_path}%' ");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (f.folder_name LIKE '%{param.keyword}%' OR f.folder_note LIKE '%{param.keyword}%') ");
            sb.Append($")folder WHERE folder.num>{(param.page - 1) * param.rows}");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<MyFloderModel>(dt);
        }
        /// <summary>
        ///  获取我的课程（目录）列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<MyCourseList> GetMyCourseList(MyCourseParam param, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} folder_id,folder.folder_name,work_num,record_work_num,record_num,
            ISNULL(record_time,0)AS record_time,ISNULL(ok_num,0)AS ok_num,ISNULL(error_num,0)AS error_num,trialNum,
            folder_url,folder_path,num FROM(SELECT folder_id,folder_name,work_num=(SELECT COUNT(0) FROM dbo.T_DocumentInfo WHERE file_status=5 AND if_remove=0 AND file_id IN(
             SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
            record_work_num=(SELECT COUNT(DISTINCT file_id) FROM dbo.T_VLionsRecord WHERE file_id IN(
             SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),	
            record_num=(SELECT COUNT(0) FROM dbo.T_VLionsRecord WHERE file_id IN(
             SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
            record_time=(SELECT SUM(em_time) FROM dbo.T_VLionsRecord WHERE file_id IN(
             SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
            record_rate=0,trialNum=(SELECT COUNT(0) FROM 
             (SELECT file_id,folder_path FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')r INNER JOIN 
             (SELECT file_id FROM dbo.T_VLionsRecord WHERE user_id={user_id})d ON r.file_id=d.file_id),
            ok_num=(SELECT SUM(result_ok)AS result_ok FROM T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),
            error_num=(SELECT SUM(result_error)AS result_ok FROM T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),folder_url,folder_path,
            ROW_NUMBER() OVER(ORDER BY update_date DESC)AS num 
            FROM dbo.T_DocumentFolder f WHERE if_remove=0 AND group_name='course' ");
   //         sb.Append($@"SELECT TOP {param.rows} folder_id,folder.folder_name,work_num,record_work_num,record_num,
   //         ISNULL(record_time,0)AS record_time,ISNULL(ok_num,0)AS ok_num,ISNULL(error_num,0)AS error_num,
   //         folder_url,folder_path,num FROM(SELECT f.folder_id,f.folder_name,work_num=(SELECT COUNT(0) FROM dbo.T_DocumentInfo 
   //         WHERE file_status=5 AND if_remove=0 AND file_id IN(
	  //          SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
	  //      record_work_num=(SELECT COUNT(DISTINCT file_id) FROM dbo.T_VLionsRecord WHERE file_id IN(
	  //          SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),	
   //         record_num=(SELECT COUNT(0) FROM dbo.T_VLionsRecord WHERE file_id IN(
	  //          SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
   //         record_time=(SELECT SUM(em_time) FROM dbo.T_VLionsRecord WHERE file_id IN(
	  //          SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),record_rate=0,
   //         ok_num=(SELECT SUM(result_ok)AS result_ok FROM T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),
   //         error_num=(SELECT SUM(result_error)AS result_ok FROM T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),f.folder_url,f.folder_path,
   //         ROW_NUMBER() OVER(ORDER BY f.update_date DESC)AS num   
	  //           FROM dbo.T_DocumentFolder a INNER JOIN
			//dbo.T_DocumentFolder f ON f.folder_path LIKE a.folder_path+'%' WHERE a.if_remove=0 AND a.group_name='course'");
            string type1 = $" (create_id={user_id} AND if_private=1) ",
                type3 = $" folder_id IN(SELECT folder_id FROM T_VRUserFolder WHERE user_guid='{param.userid}') ";
            switch (param.type)
            {
                case 1: sb.Append($" AND {type1} "); break;
                case 3: sb.Append($" AND {type3} "); break;
                default: sb.Append($" AND ({type1} OR {type3}) "); break;
            }
            sb.Append($" )folder WHERE folder.num>{(param.page - 1) * param.rows} ");
            if (param.type == 2)
                sb.Append($" AND folder.trialNum>0 ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<MyCourseList>(dt);
        }
        /// <summary>
        ///  获取最新的作品
        /// </summary>
        /// <param name="folder_path"></param>
        /// <returns></returns>
        public List<ModifyGoodList> GetMyModifyGoodList(string folder_path)
        {
            sb?.Clear();
            sb.Append($@"SELECT *,file_price=ISNULL((SELECT TOP 1 ori_price FROM dbo.T_VRGoodDisiPrice WHERE ori_price>0 AND file_id=i.file_id),0) FROM (
            SELECT ROW_NUMBER() OVER(PARTITION BY rel.folder_id ORDER BY info.update_time DESC)AS num,info.file_id,
            rel.folder_id,info.file_name,info.file_note FROM
            (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{folder_path}%')rel INNER JOIN
            (SELECT file_id,file_name,file_note,update_time FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5)info 
             ON info.file_id=rel.file_id)i WHERE i.num<3;");
            return SqlHelper.ExecuteList<ModifyGoodList>(sb.ToString());
        }
        /// <summary>
        ///  获取店铺下最新的两个商品【包含子目录】
        /// </summary>
        /// <param name="folder"></param>
        /// <returns></returns>
        public List<MyFloderGoodsList> GetGoodsByFolders(string folder_path,int fileListNum=2)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {fileListNum} file_id AS fileID,file_name AS fileName,file_note AS fileNote,update_time,
            fileImage=(SELECT TOP 1 file_url FROM T_DocBagFileRecord WHERE file_id=i.file_id AND file_type=0),
            filePrice=(CASE WHEN i.isEntity=1 THEN (SELECT MIN(specMoney) FROM dbo.T_VRDDSpecPrice WHERE fileId=i.file_id)
            ELSE (SELECT MIN(ori_price) FROM dbo.T_VRGoodDisiPrice WHERE file_id=i.file_id) END) FROM dbo.T_DocumentInfo i 
              WHERE  if_remove=0 AND file_status=5 AND file_id IN(
              SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{folder_path}%')  AND i.page_type='DDCircle' ORDER BY i.update_time;");
            return SqlHelper.ExecuteList<MyFloderGoodsList>(sb.ToString());
        }
    }
}